/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package basedatos;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author yojugracu
 */
public class DML {

    //--------------- Realiza la seleccion conforme a un sql -----------------//
    public final static ArrayList<String[]> seleccionar( Connection conexion, 
            String sql ){
        try{
            Statement stmt = conexion.createStatement();
            ResultSet rs = stmt.executeQuery( sql );
            ResultSetMetaData rsmd = rs.getMetaData();
            ArrayList<String[]> lista = new ArrayList<String[]>();
            int ncolumnas = rsmd.getColumnCount();
            
            while( rs.next() ){
                String[] fila = new String[ rsmd.getColumnCount() ];
                for( int i = 0 ; i < ncolumnas ; i++ ){
                    fila[ i ] = rs.getString( i + 1 );
                }
                lista.add( fila );
            }
            return lista;
        }catch( Exception e ){ e.printStackTrace(); return null; }
    }
    //------------------------------------------------------------------------//
    
    //Seleccionar por medio de especificacion de campos, tablas y condiciones//
    public final static ArrayList<String[]> select( Connection conexion, 
            String campos, String tablas, String condiciones )
    {
        String sql = "SELECT " + campos + " FROM " + tablas + " WHERE " + 
                condiciones;
        return seleccionar( conexion, sql );
    }
    //------------------------------------------------------------------------//
    
    //--------------- Seleccionar por medio de campos y tablas ---------------//
    public final static ArrayList<String[]> select( Connection conexion, 
            String campos, String tablas )
    {
        String sql = "SELECT " + campos + " FROM " + tablas;
        return seleccionar( conexion, sql );
    }
    //------------------------------------------------------------------------//
    
    //-------------------- Seleccionar todo de las tablas --------------------//
    public final static ArrayList<String[]> select( Connection conexion, 
            String tablas )
    {
        String sql = "SELECT * FROM " + tablas; 
        return seleccionar( conexion, sql );
    }
    //------------------------------------------------------------------------//
    
    //--- Obtiene las tablas que existen en el esquema de la base de datos ---//
    public final static String[] getTablas( Connection conexion ){
        try{
            ArrayList<String> tablas;
            
            Statement stmt = conexion.createStatement();
            
            String sql = "SELECT table_name "
                    + "FROM information_schema.tables "
                    + "WHERE table_schema = 'public';"; 
            ResultSet rs = stmt.executeQuery( sql );
            
            tablas = new ArrayList<String>();
            
            while ( rs.next() ) {
                tablas.add( rs.getString( 1 ) );
            }
            rs.close();
            stmt.close();
            return ( String[] )tablas.toArray( new String[ tablas.size() ] );
            
        }catch( Exception e ){  e.printStackTrace(); return null; }
    }
    //------------------------------------------------------------------------//
    
    //------ Esta funcion sirve para obtener los campos de la tabla junto-----//
    //------------------------ con el tipo del campo -------------------------//
    public final static ArrayList<String[]> getCamposTabla( Connection conexion, 
            String tabla )
    {
        try{
            Statement stmt = conexion.createStatement();
            
            String sql = "SELECT column_name, data_type FROM "
                    + "INFORMATION_SCHEMA.COLUMNS where table_name = '" + tabla + "'"; 
            ResultSet rs = stmt.executeQuery( sql );
            
            ArrayList<String> primaria = getPrimaryKey( conexion, tabla );
            ArrayList<String> foreing = getForeignKey(conexion, tabla);
            
            ArrayList<String[]> lista = new ArrayList<String[]>();
            while ( rs.next() ) {
                String nom_campo = rs.getString(1);
                lista.add( new String[]{ esLlave(primaria, foreing, nom_campo), 
                    nom_campo, rs.getString(2), "" } );
            }
            rs.close();
            stmt.close();
            
            return lista;
        }catch( Exception e ){  e.printStackTrace(); return null; }
    }
    //------------------------------------------------------------------------//

    //--------------- Devuelve el tipo de llave del campo --------------------//
    public final static String esLlave( ArrayList<String> primaria, 
            ArrayList<String> foreing, String campo )
    {
        for( String p : primaria ){
            if( p.equals( campo ) ) return "PK";
        }

        for( String f : foreing ){
            if( f.equals( campo ) ) return "FK";
        }
        return "--";
    }
    //------------------------------------------------------------------------//
    
    //-------- Obtiene todas las llaves primarias de la tabla ----------------//
    public final static ArrayList<String> getPrimaryKey( Connection conexion, 
            String tabla )
    {
        try{
            Statement stmt = conexion.createStatement();
            String sql = "SELECT pg_get_constraintdef(pg_constraint.oid), "
                    + "pg_class.relname AS tabla, pg_constraint.conname FROM "
                    + "pg_constraint, pg_class WHERE ((pg_constraint.contype"
                    + " = 'p'::\"char\") AND (pg_constraint.conrelid = "
                    + "pg_class.oid)) and relname='" + tabla + "'";
            ResultSet rs = stmt.executeQuery( sql );
            ArrayList<String> primaria = new ArrayList<String>();
            while( rs.next() ){
                String primarias = rs.getString( 1 );
                primarias = primarias.replace( "PRIMARY KEY (", "" );
                primarias = primarias.replace( ")", "" );
                String[] prim = primarias.split(",");
                for( String p : prim ){
                    primaria.add( p.trim() );
                }
            }
            rs.close();
            stmt.close();
            return primaria;
        }catch( Exception e ){ e.printStackTrace(); return null; }
    }
    //------------------------------------------------------------------------//
    
    //--------------------- Obtiene todas las llaves foraneas ----------------//
    public final static ArrayList<String> getForeignKey( Connection conexion, 
            String tabla )
    {
        try{
            Statement stmt = conexion.createStatement();
            String sql = "SELECT pg_get_constraintdef(pg_constraint.oid), "
                    + "pg_class.relname AS tabla, pg_constraint.conname FROM "
                    + "pg_constraint, pg_class WHERE ((pg_constraint.contype"
                    + " = 'f'::\"char\") AND (pg_constraint.conrelid = "
                    + "pg_class.oid)) and relname='" + tabla + "'";
            ResultSet rs = stmt.executeQuery( sql );
            ArrayList<String> foreign = new ArrayList<String>();
            while( rs.next() ){
                String foreigns = rs.getString( 1 );
                String[] s = foreigns.split( "REFERENCES" );
                foreigns = s[0].replace( "FOREIGN KEY (", "" );
                foreigns = foreigns.replace( ")", "" );
                String[] fs = foreigns.split( "," );
                for( String nf : fs ){
                    foreign.add( nf.trim() );
                }
            }
            rs.close();
            stmt.close();
            return foreign;
        }catch( Exception e ){ e.printStackTrace(); return null; }
    }
    //------------------------------------------------------------------------//
    
    //-------------- Obtiene las relaciones que tiene una tabla --------------//
    public final static ArrayList<String[]> getRelacionTabla( Connection conexion, 
            String tabla )
    {
        try{           
            Statement stmt = conexion.createStatement();
            
            String sql = "SELECT pg_constraint.conname as nombrellave , "
                    + "pg_class.relname AS tabla, (select a.relname from "
                    + "pg_class a where a.oid=pg_constraint.confrelid) as "
                    + "tabla_foranea FROM pg_constraint, pg_class WHERE "
                    + "((pg_constraint.contype = 'f'::\"char\") AND "
                    + "(pg_constraint.conrelid = pg_class.oid) and relname='"
                    + tabla + "')";
            ResultSet rs = stmt.executeQuery( sql );
            ArrayList<String[]> relacion = new ArrayList<String[]>();
            while ( rs.next() ) {
                relacion.add( new String[]{ rs.getString(1), rs.getString(2), 
                    rs.getString(3) } );
            }
            rs.close();
            stmt.close();
            
            return relacion;

        }catch( Exception e ){  e.printStackTrace(); return null; }
    }
    //------------------------------------------------------------------------//
    
    //------------ Obtiene la relaciones de un conjunto de tablas ------------//
    public final static ArrayList<String[]> getRelaciones( Connection conexion, 
            String[] tablas )
    {    
        ArrayList<String[]> relaciones = new ArrayList<String[]>();
        ArrayList<String[]> relacion;
        for( String tabla : tablas )
        {
            relacion = getRelacionTabla( conexion, tabla );
            if( relacion != null )
            {
                for( int i = 0 ; i < relacion.size() ; i++ ){
                    relaciones.add( relacion.get( i ) );
                }
            }
        }
        
        return relaciones;
    }
    //------------------------------------------------------------------------//
}
